import pandas as pd
import numpy as np
from datetime import datetime
import matplotlib.pyplot as plt

# ======================================
# 请在此处配置你的输入信息
# ======================================
INPUT_FILE_PATH = r'C:\Users\20593\Downloads\Food_Log_001.csv'#csv路径
OUTPUT_FILE_PATH = r'D:\2025小学期\Food_Log_001.csv'#保存处理后的文件路径
TIMESTAMP_COLUMN = 'time'#文件里时间的列名
VALUE_COLUMNS = ['amount','calorie']  #需要处理的列
VISUALIZE_RESULTS = True

# ======================================
# 数据处理参数配置
# ======================================
OUTLIER_METHOD = 'clip'
OUTLIER_THRESHOLD = 3.0
MISSING_METHOD = 'interpolate'
RESAMPLE_METHOD = 'mean'


def load_data(file_path):
    """加载数据文件，根据文件扩展名自动选择读取方法"""
    try:
        if file_path.endswith('.csv'):
            data = pd.read_csv(file_path, low_memory=False)
        elif file_path.endswith(('.xlsx', '.xls')):
            data = pd.read_excel(file_path)
        elif file_path.endswith('.parquet'):
            data = pd.read_parquet(file_path)
        else:
            raise ValueError(f"不支持的文件格式: {file_path}")
        print(f"数据加载成功，共{len(data)}行，{len(data.columns)}列")
        return data
    except Exception as e:
        print(f"数据加载失败: {e}")
        return None


def process_timestamp(data, timestamp_col):
    """处理时间戳列（兼容字符串、数值格式）"""
    df = data.copy()

    # 分情况处理时间戳：字符串 / 数值
    if pd.api.types.is_string_dtype(df[timestamp_col]):
        # 情况1：字符串格式（如 "2020/2/13 15:28"）
        df[timestamp_col] = pd.to_datetime(df[timestamp_col])  # 自动解析字符串时间
    elif pd.api.types.is_numeric_dtype(df[timestamp_col]):
        # 情况2：数值格式（毫秒/秒级时间戳）
        if df[timestamp_col].max() > 946684800000:  # 2000-01-01的毫秒数
            df[timestamp_col] = pd.to_datetime(df[timestamp_col], unit='ms')
        else:
            df[timestamp_col] = pd.to_datetime(df[timestamp_col], unit='s')
    else:
        # 既不是字符串也不是数值，无法转换
        raise TypeError(f"时间列 '{timestamp_col}' 类型无效！当前类型: {df[timestamp_col].dtype}")

    # 设置时间索引 + 排序
    df.set_index(timestamp_col, inplace=True)
    df.sort_index(inplace=True)

    # 强制检查索引类型（确保转换成功）
    if not isinstance(df.index, pd.DatetimeIndex):
        raise ValueError(
            f"时间索引转换失败！\n"
            f"请检查 '{timestamp_col}' 列的时间格式（如是否包含完整日期+时间）。\n"
            f"示例有效格式：2020-02-13 15:28:00、2020/2/13 15:28"
        )

    return df

def handle_outliers(df, value_cols, method='clip', threshold=3.0):
    """处理异常值"""
    result = df.copy()
    for col in value_cols:
        if col in result.columns:
            mean = result[col].mean()
            std = result[col].std()
            if std == 0:
                continue
            z_score = (result[col] - mean) / std
            if method == 'clip':
                lower, upper = mean - threshold*std, mean + threshold*std
                result[col] = result[col].clip(lower, upper)
            elif method == 'remove':
                result.loc[abs(z_score) > threshold, col] = np.nan
            elif method == 'interpolate':
                result.loc[abs(z_score) > threshold, col] = np.nan
                result[col] = result[col].interpolate(method='time')
            else:
                print(f"未知的异常值处理方法: {method}")
    return result


def fill_missing_values(df, value_cols, method='interpolate'):
    """填充缺失值"""
    result = df.copy()
    for col in value_cols:
        if col in result.columns:
            if method == 'ffill':
                result[col] = result[col].fillna(method='ffill')
            elif method == 'bfill':
                result[col] = result[col].fillna(method='bfill')
            elif method == 'interpolate':
                result[col] = result[col].interpolate(method='time')
            else:
                print(f"未知的缺失值填充方法: {method}")
    result.dropna(subset=value_cols, inplace=True)
    return result


def resample_data(df, value_cols, rule='H', method='mean'):
    """将数据降采样为每小时一条记录"""
    resampled_df = pd.DataFrame()
    for col in value_cols:
        if col in df.columns:
            if method == 'mean':
                resampled_df[col] = df[col].resample(rule).mean()
            elif method == 'sum':
                resampled_df[col] = df[col].resample(rule).sum()
            elif method == 'max':
                resampled_df[col] = df[col].resample(rule).max()
            elif method == 'min':
                resampled_df[col] = df[col].resample(rule).min()
            else:
                print(f"未知的降采样方法: {method}")
    resampled_df.dropna(how='all', inplace=True)
    return resampled_df


def visualize_results(original_df, processed_df, value_cols, sample_size=1000):
    """可视化处理前后的数据对比"""
    if not value_cols or not isinstance(value_cols, list):
        print("没有需要可视化的列")
        return
    original_sample = original_df.iloc[-sample_size:] if len(original_df) > sample_size else original_df
    processed_sample = processed_df.iloc[-int(sample_size/10):] if len(processed_df) > sample_size/10 else processed_df
    fig, axes = plt.subplots(len(value_cols), 2, figsize=(15, 4 * len(value_cols)))
    if len(value_cols) == 1:
        axes = [axes]
    for i, col in enumerate(value_cols):
        if col in original_sample.columns and col in processed_sample.columns:
            ax1, ax2 = axes[i]
            original_sample[col].plot(ax=ax1, title=f'原始数据 - {col}', grid=True)
            processed_sample[col].plot(ax=ax2, title=f'处理后数据 - {col}', grid=True, marker='o', markersize=4)
            ax1.set_xlabel('时间'), ax1.set_ylabel('值')
            ax2.set_xlabel('时间'), ax2.set_ylabel('值')
    plt.tight_layout()
    plt.show()


def main():
    """主函数，执行完整的数据处理流程"""
    print(f"===== 开始数据处理 =====")
    print(f"时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    data = load_data(INPUT_FILE_PATH)
    if data is None:
        print("数据加载失败，程序终止")
        return
    print(f"正在处理时间戳列 '{TIMESTAMP_COLUMN}'...")
    processed_data = process_timestamp(data, TIMESTAMP_COLUMN)
    print(f"正在处理异常值（方法: {OUTLIER_METHOD}, 阈值: {OUTLIER_THRESHOLD}）...")
    processed_data = handle_outliers(processed_data, VALUE_COLUMNS, method=OUTLIER_METHOD, threshold=OUTLIER_THRESHOLD)
    print(f"正在填充缺失值（方法: {MISSING_METHOD}）...")
    processed_data = fill_missing_values(processed_data, VALUE_COLUMNS, method=MISSING_METHOD)
    print(f"正在进行降采样（方法: {RESAMPLE_METHOD}, 频率: 小时）...")
    hourly_data = resample_data(processed_data, VALUE_COLUMNS, rule='H', method=RESAMPLE_METHOD)
    print(f"正在保存结果到 {OUTPUT_FILE_PATH}...")
    hourly_data.to_csv(OUTPUT_FILE_PATH)
    print(f"数据处理完成！原始数据: {len(data)} 行 → 处理后数据: {len(hourly_data)} 行")
    if VISUALIZE_RESULTS:
        print("正在生成可视化图表...")
        visualize_results(processed_data, hourly_data, VALUE_COLUMNS)


if __name__ == "__main__":
    main()